<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"
    import="java.sql.*"
    
    %>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String url = "jdbc:oracle:thin:@192.168.3.24:1521:xe";
String id = "test";
String pwd = "test";
String driver = "oracle.jdbc.driver.OracleDriver";
String query = "SELECT DEPTNAME,DEPTNO,DEPTLEV,DEPTSTEP,CLASSCODE"+
				",substr(to_char(DEPTSTDATE,'YYYYMMDD'),0,8) DEPTSTDATE"+
				",substr(to_char(DEPTENDATE,'YYYYMMDD'),0,8) DEPTENDATE"+
				",DEPTPRCODE,DEPTCHIEF,ETC"+
				"FROM DEPTINFO";
try{
	Class.forName(driver);
	conn = DriverManager.getConnection(url,id,pwd);
	pstmt = conn.prepareStatement(query);
	pstmt.setString(1, request.getParameter("dept_name"));
	rs = pstmt.executeQuery();
	
	
	System.out.println("{");	
	System.out.println("	DATA:[[");
	
	//JSON 문자열 생성
	out.println("{");	
	out.println("	DATA:[[");
	while(rs.next()){
		//둘중에 하나를 사용할 것.
		//1. 컬럼 순서에 맞춰 데이터 생성
// 		out.println("{C1:'',C2:'',C3:'"+rs.getString("JOB")+"',C4:'"+rs.getString("DEPTNO")+"'");			
// 		out.println(",C5:'"+rs.getString("EMPNO")+"',C6:'"+rs.getString("ENAME")+"',C7:'"+rs.getString("HIREDATE")+"'");
// 		out.println(",C8:'"+rs.getString("MGR")+"',C9:'"+rs.getString("SAL")+"',C10:'"+rs.getString("COMM")+"'},");
		//2. SaveName을 이용한 방법
		out.println("{DEPTNAME:'"+rs.getString("DEPTNAME")+"',DEPTNO:'"+rs.getString("DEPTNO")+"',DEPTLEV:'"+rs.getString("DEPTLEV")+"' ");
		out.println(",DEPTSTEP:'"+rs.getString("DEPTSTEP")+"',CLASSCODE:'"+rs.getString("CLASSCODE")+"'");
		out.println(",DEPTSTDATE:'"+rs.getString("DEPTSTDATE")+"',DEPTENDATE:'"+rs.getString("DEPTENDATE")+"'");
		out.println(",DEPTPRCODE:'"+rs.getString("DEPTPRCODE")+"',DEPTCHIEF:'"+rs.getString("DEPTCHIEF")+"',ETC:'"+rs.getString("ETC")+"'},");		

		System.out.println("{DEPTNAME:'"+rs.getString("DEPTNAME")+"',DEPTNO:'"+rs.getString("DEPTNO")+"',DEPTLEV:'"+rs.getString("DEPTLEV")+"' ");
		System.out.println(",DEPTSTEP:'"+rs.getString("DEPTSTEP")+"',CLASSCODE:'"+rs.getString("CLASSCODE")+"'");
		System.out.println(",DEPTSTDATE:'"+rs.getString("DEPTSTDATE")+"',DEPTENDATE:'"+rs.getString("DEPTENDATE")+"'");
		System.out.println(",DEPTPRCODE:'"+rs.getString("DEPTPRCODE")+"',DEPTCHIEF:'"+rs.getString("DEPTCHIEF")+"',ETC:'"+rs.getString("ETC")+"'},\n\n");
	}
	out.println("]]}");		

	System.out.println("]]}");	
	
	//JSON 문자열 생성


	

	
	
}catch(Exception ex){
	String msg = ex.getMessage();
	//오류 발생
	out.println("{");
	out.println("Result : {Code:-1, Message:'오류가 발생하였습니다.\n"+msg+"'}");
	out.println("}");
	
}finally{
	rs.close();
	pstmt.close();
	conn.close();	
}
%>

    